package board1;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class UserDAO {
	String db_driver;
	String db_url;
	String db_id;
	String db_passwd;
	String tb_name;

	public UserDAO() {
		db_driver = "com.mysql.jdbc.Driver";
//		db_url = "jdbc:mysql://localhost/android?useUnicode=true&characterEncoding=utf-8";
//		db_id = "root";
//		db_passwd = "12345";
		
		db_url = "jdbc:mysql://localhost/auralizer?useUnicode=true&characterEncoding=utf-8";
		db_id = "auralizer";
		db_passwd = "!234asdf";
		
		tb_name = "sa_login";
	}
	
	public boolean insertUser(UserTO to) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		boolean flag = false;
		
		try {		
			Class.forName(db_driver); 
			conn = DriverManager.getConnection(db_url, db_id, db_passwd);
			
//			String sql = "insert into " + tb_name + " values (0, ?, ?, ?, ?, ?, ?, 0, now())";
			String sql = "insert into " + tb_name + " values (?, ?, ?, ?)";
			
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, to.getPhone_number());
			pstmt.setString(2, to.getChon_id());
			pstmt.setString(3, to.getUser_name());
			pstmt.setString(4, to.getUser_lv());
//			pstmt.setString(5, to.getContent());
//			pstmt.setString(6, to.getWip());
			
			pstmt.executeUpdate();
			
			flag = true;
			
			System.out.println(" user : "+flag);
		} catch(ClassNotFoundException e) {
		} catch(SQLException e) {
			
			flag = false;
			flag = modifyUser(to);
			System.out.println(" Exception user : "+flag);
			
		} finally {
			if(pstmt != null) try { pstmt.close(); } catch(SQLException e) {}
			if(conn != null) try { conn.close(); } catch(SQLException e) {}
		}
		return flag;
	}

	public ArrayList<UserTO> listUser() {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		ArrayList<UserTO> data = new ArrayList<UserTO>();
		
		try {
			Class.forName(db_driver);
			conn = DriverManager.getConnection(db_url, db_id, db_passwd);
			
			String sql = "select phone_number, chon_id, user_name, user_lv from " + tb_name + " order by phone_number desc";
			pstmt = conn.prepareStatement(
						sql,
						ResultSet.TYPE_SCROLL_INSENSITIVE,
						ResultSet.CONCUR_READ_ONLY
					);
			
			rs = pstmt.executeQuery();

			while(rs.next()) {
				UserTO to = new UserTO();
				to.setPhone_number(rs.getString("phone_number"));
				to.setChon_id(rs.getString("chon_id"));
				to.setUser_name(rs.getString("user_name"));
				to.setUser_lv(rs.getString("user_lv"));
//				to.setHit(rs.getString("hit"));
				data.add(to);
			}
		} catch(ClassNotFoundException e) {
		} catch(SQLException e) {
		} finally {
			if(rs != null) try { rs.close(); } catch(SQLException e) {}
			if(pstmt != null) try { pstmt.close(); } catch(SQLException e) {}
			if(conn != null) try { conn.close(); } catch(SQLException e) {}
		}
		return data;
	}
	
	public UserListTO listUser(UserListTO tos) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		int cpage = tos.getCpage();
		int recordPerPage = tos.getRecordPerPage();
		int blockPerPage = tos.getBlockPerPage();
		
		try {
			Class.forName(db_driver);
			conn = DriverManager.getConnection(db_url, db_id, db_passwd);
			
//			String sql = "select no, subject, writer, wdate, hit from " + tb_name + " order by no desc";
			String sql = "select phone_number, chon_id, user_name, user_lv from " + tb_name + " order by phone_number desc";
			pstmt = conn.prepareStatement(
						sql,
						ResultSet.TYPE_SCROLL_INSENSITIVE,
						ResultSet.CONCUR_READ_ONLY
					);
			
			rs = pstmt.executeQuery();
			
			rs.last();
			int totalRecord = rs.getRow();
			rs.beforeFirst();
			
			tos.setTotalPage((totalRecord > 0 && totalRecord % recordPerPage == 0) ? totalRecord/recordPerPage : totalRecord/recordPerPage + 1);
		
			int skip = (cpage - 1) * recordPerPage;
			if(skip != 0) rs.absolute(skip);
			
			ArrayList<UserTO> data = new ArrayList<UserTO>();
			for(int i=0 ; i < recordPerPage && rs.next() ; i++) {
				UserTO to = new UserTO();
				to.setPhone_number(rs.getString("phone_number"));
				to.setChon_id(rs.getString("chon_id"));
				to.setUser_name(rs.getString("user_name"));
//				to.setWdate(rs.getString("wdate"));
//				to.setHit(rs.getString("hit"));
				data.add(to);
			}
			tos.setUserList(data);
			
			tos.setStartBlock(((cpage-1) / blockPerPage) * blockPerPage + 1);
			tos.setEndBlock(((cpage-1) / blockPerPage) * blockPerPage + blockPerPage);
			if(tos.getEndBlock() >= tos.getTotalPage()) {
				tos.setEndBlock(tos.getTotalPage());
			}
		} catch(ClassNotFoundException e) {
		} catch(SQLException e) {
		} finally {
			if(rs != null) try { rs.close(); } catch(SQLException e) {}
			if(pstmt != null) try { pstmt.close(); } catch(SQLException e) {}
			if(conn != null) try { conn.close(); } catch(SQLException e) {}
		}
		return tos;
	}
	
	public UserTO viewUser(UserTO to) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
//		boolean flag = false;
		
		try {
			Class.forName(db_driver);
			conn = DriverManager.getConnection(db_url, db_id, db_passwd);
			
//			String sql = "update " + tb_name + " set hit=hit+1 where no=?";
//			pstmt = conn.prepareStatement(sql);
//			pstmt.setString(1, to.getNo());
//			
//			pstmt.executeUpdate();
			
//			String sql = "select no, subject, writer, wdate, hit from " + tb_name + " order by no desc";
			String sql = "select phone_number, chon_id, user_name, user_lv from " + tb_name + " where phone_number =?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, to.getPhone_number());
			
			rs = pstmt.executeQuery();
			if(rs.next()) {
				to.setPhone_number(rs.getString("phone_number"));
				to.setChon_id(rs.getString("chon_id"));
//				to.setMail(rs.getString("mail") == null ? "" : rs.getString("mail"));
				to.setUser_name(rs.getString("user_name"));
				to.setUser_lv(rs.getString("user_lv"));
//				to.setHit(rs.getString("hit"));
//				to.setContent(rs.getString("content") == null ? "" : rs.getString("content").replaceAll("\n", "<br>"));
//				flag = true;
			}
		} catch(ClassNotFoundException e) {
		} catch(SQLException e) {
		} finally {
			if(rs != null) try { rs.close(); } catch(SQLException e) {}
			if(pstmt != null) try { pstmt.close(); } catch(SQLException e) {}
			if(conn != null) try { conn.close(); } catch(SQLException e) {}
		}
		return to;
	}
	
	public UserTO modifyFormUser(UserTO to) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			Class.forName(db_driver);
			conn = DriverManager.getConnection(db_url, db_id, db_passwd);
			
//			String sql = "select subject, writer, mail, content from " + tb_name + " where no=?";
			String sql = "select chon_id, user_name, user_lv from " + tb_name + " where phone_number=?";
			pstmt = conn.prepareStatement(sql);
//			pstmt.setString(1, to.getNo());
			
			rs = pstmt.executeQuery();
			if(rs.next()) {
				to.setChon_id(rs.getString("chon_id"));
				to.setUser_name(rs.getString("user_name"));
				to.setUser_lv(rs.getString("user_lv"));
//				to.setUser_lv(rs.getString("mail") == null ? "" : rs.getString("mail"));
//				to.setContent(rs.getString("content") == null ? "" : rs.getString("content"));
			}
		} catch(ClassNotFoundException e) {	
		} catch(SQLException e) {
		} finally {
			if(rs != null) try { rs.close(); } catch(SQLException e) {}
			if(pstmt != null) try { pstmt.close(); } catch(SQLException e) {}
			if(conn != null) try { conn.close(); } catch(SQLException e) {}
		}
		return to;
	}
	
	public UserTO deleteFormUser(UserTO to) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		try {
			Class.forName(db_driver);
			conn = DriverManager.getConnection(db_url, db_id, db_passwd);
			
//			String sql = "select subject, writer, mail from " + tb_name + " where no=?";
			String sql = "select chon_id, user_name, user_lv from " + tb_name + " where phone_number=?";
			pstmt = conn.prepareStatement(sql);
//			pstmt.setString(1, to.getNo());
			
			rs = pstmt.executeQuery();
			if(rs.next()) {
				to.setUser_name(rs.getString("user_name"));
				to.setChon_id(rs.getString("chon_id"));
				to.setUser_lv(rs.getString("user_lv"));
//				to.setMail(rs.getString("mail") == null ? "" : rs.getString("mail"));
			}
		} catch(ClassNotFoundException e) {
		} catch(SQLException e) {
		} finally {
			if(rs != null) try { rs.close(); } catch(SQLException e) {}
			if(pstmt != null) try { pstmt.close(); } catch(SQLException e) {}
			if(conn != null) try { conn.close(); } catch(SQLException e) {}
		}
		return to;
	}

	public boolean modifyUser(UserTO to) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		boolean flag = false;
		
		try {
			Class.forName(db_driver);
			conn = DriverManager.getConnection(db_url, db_id, db_passwd);
			
//			String sql = "update " + tb_name + " set subject=?, mail=?, content=? where no=? and passwd=?";
			String sql = "update " + tb_name + " set chon_id=?, user_name=?, user_lv=? where phone_number=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, to.getChon_id());
			pstmt.setString(2, to.getUser_name());
			pstmt.setString(3, to.getUser_lv());
			pstmt.setString(4, to.getPhone_number());
//			pstmt.setString(5, to.getPasswd());
			
			pstmt.executeUpdate();
			flag = true;
			
		} catch(ClassNotFoundException e) {
		} catch(SQLException e) {
		} finally {
			if(pstmt != null) try { pstmt.close(); } catch(SQLException e) {}
			if(conn != null) try { conn.close(); } catch(SQLException e) {}
		}
		return flag;
	}

	public int deleteUser(UserTO to) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		int flag = 2;
		
		try {
			Class.forName(db_driver);
			conn = DriverManager.getConnection(db_url, db_id, db_passwd);
			
			String sql = "delete from " + tb_name + " where phone_number=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, to.getPhone_number());
//			pstmt.setString(2, to.getPasswd());
			
			if(pstmt.executeUpdate() == 1) {
				flag = 0;
			} else {
				flag = 1;
			}
		} catch(ClassNotFoundException e) {	
		} catch(SQLException e) {
		} finally {
			if(pstmt != null) try { pstmt.close(); } catch(SQLException e) {}
			if(conn != null) try { conn.close(); } catch(SQLException e) {}
		}
		return flag;
	}

}






